package com.niit.dao;

import com.niit.entity.Books;
import com.niit.entity.Users;
import com.niit.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class UsersDao {
//
    //    方法要用来做什么？---验证登录是否成功
//    方法要给谁访问？---public
//    方法执行之后有什么返回值？---Boolean
//    方法执行需要传入什么参数？---手机号，密码
    public Boolean login(String phoneNum,String password) throws SQLException {
        ResultSet rs = JDBCUtils.executeQuery(
                "SELECT * FROM users where phone_num = ? and `password` = ?", phoneNum, password);
        return  rs.next();
    }


    public Boolean hasUser(String phoneNum) throws SQLException {
       return JDBCUtils.executeQuery("SELECT * from users where phone_num = ?", phoneNum).next();
    }

    public Boolean addUser(String phoneNum, String password) {
        int i = JDBCUtils.executeUpdate(
                "INSERT INTO users (phone_num,`password`,create_time,role)VALUES(?,?,now(),1);"
                , phoneNum, password);
        return i>0;
    }

    /**
     * 更新用户
     * @param password 密码
     * @param gender 性别
     * @param birthday 生日
     * @param userId 用户id
     * @return
     */
    public Boolean updateUser(String password, Integer gender, Date birthday, Integer userId){
        int i = JDBCUtils.executeUpdate(
                "UPDATE users set `password` = ?, gender = ?,birthday = ? where user_id = ?");
        return i>0;
    }

    /**
     * 删除用户
     * @param userId 用户id
     * @return
     */
    public Boolean deleteUser(Integer userId){
        int i = JDBCUtils.executeUpdate(
                "DELETE FROM users WHERE user_id = ?");
        return i>0;
    }

    /*
    1.分页查询
    2.模糊搜索
    3.多条件查询
     */

    public List<Users> listUsers(String phoneNum,Integer gender,Integer role ,Integer page,Integer pageSize) throws SQLException {
        StringBuilder sql = new StringBuilder("select * from users where 1=1");
        if(phoneNum !=null && !phoneNum.equals("")){
//            通过字符串拼接sql语句可能会导致SQL注入
            sql.append(" and phone_num like '%").append(phoneNum).append("%'");
        }
        if(gender !=null){
            sql.append(" and gender = ").append(gender);
        }
        if(role !=null){
            sql.append(" and role = ").append(role);
        }
        if(page !=null && pageSize !=null){
            sql.append(" limit ").append((page-1)*pageSize).append(",").append(pageSize);
        }
        Connection connection = JDBCUtils.getConnection();
        Statement statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(sql.toString());
        List<Users> usersList = new ArrayList<>();
        while (rs.next()){
            Users users = new Users();
            users.setUserId(rs.getInt("user_id"));
            users.setPhoneNum(rs.getString("phone_num"));
            users.setPassword(rs.getString("password"));
            users.setGender(rs.getInt("gender")+"");
            users.setBirthday(rs.getDate("birthday"));
            users.setCreateTime(rs.getDate("create_time"));
            users.setRole(rs.getInt("role"));
            usersList.add(users);
        }
        JDBCUtils.close(connection,statement,rs);
        return usersList;

    }






}
